package cn.antia.cwin.db;

import cn.antia.db.*;
import cn.antia.cwin.Cele;
import cn.antia.cwin.DBField;
import cn.antia.cep.db.DBException;
import cn.antia.cep.common.SysExceptionTool;
import cn.antia.cep.common.AntiaLog;

import java.sql.*;
import java.util.*;

/**
 * User: Jacob Xiao
 * Date: 2008-7-13
 * Time: 20:12:41
 */
public class CwinDAO extends AbstractDAO{
    public Connection getDBConnection() {
        return null;
    }
    
    private CwinSQLGene cwinSqlGene;

    public CwinDAO() {
        super();
        AntiaLog.debug("aaaaaaaaaaaaaaaaaaaaaaaaaaaaa --- DAO inited");
        setSqlGene(new MySQLSQLGene());
        cwinSqlGene = new MySQLCwinSQLGene();
    }

    public Map<Cele,Object> get(String table, int id) {
        String sql = cwinSqlGene.getSql(table, id);
        List<Map<Cele,Object>> l = find(sql);
        if (l == null || l.size() < 1) {
            return null;
        }
        return l.get(0);
    }

    public List<Map<Cele,Object>> find(String table, Map<Cele,Object> conditions) throws DBException {
        JdbcSql sql = cwinSqlGene.findSql(table, conditions);
        return find(sql);
    }
    
    public List<Map<Cele,Object>> find(String table,Map<Cele,Object> conditions,String orderField,boolean isAsc,int start, int end) throws DBException {
        JdbcSql sql = cwinSqlGene.findSql(table,conditions,orderField,isAsc,start,end);
        return find(sql);
	}

    public List<Map<Cele,Object>> findPage(String table,Map<Cele,Object> conditions,String orderField,boolean isAsc,int page, int pageSize) throws DBException {
        JdbcSql sql = cwinSqlGene.findSqlPage(table,conditions,orderField,isAsc,page,pageSize);
        return find(sql);
	}

    public List<Map<Cele,Object>> find(String table, List<String> fields,Map<Cele,Object> conditions) throws DBException {
        JdbcSql sql = cwinSqlGene.findSql(table, fields, conditions);
        return find(sql);
    }
    /**
	 * 查询
	 * @param table		表名
	 * @param fields	要查询的字段
	 * @param conditions	查询条件
	 * @param orderField order field
     * @param isAsc is asc?
     * @param start 开始记录
	 * @param end 结束记录
	 * @return	结果列表:List<Map<Cele,Object>>
	 * @throws DBException e
	 */
    public List<Map<Cele,Object>> find(String table, List<String> fields,Map<Cele,Object> conditions,String orderField,boolean isAsc,int start, int end) throws DBException {
        JdbcSql sql = cwinSqlGene.findSql(table, fields,conditions,orderField,isAsc,start,end);
        return find(sql);
	}
    public List<Map<Cele,Object>> findPage(String table, List<String> fields,Map<Cele,Object> conditions,String orderField,boolean isAsc,int page, int pageSize) throws DBException {
        JdbcSql sql = cwinSqlGene.findSqlPage(table, fields,conditions,orderField,isAsc,page,pageSize);
        return find(sql);
	}
    /**
     * 根据SQL,返回指定表的结构
     * 此方法只适用于MYSQL数据库
     * @param table:表名
     * @return LIST中所存对象是List<ElementComsat>
     */
    public List<Cele> findTableDesc(String table) throws DBException {
        String sql = "desc " + table;
        Cele co = new Cele(); // 通讯对象
        List<Cele> elements = new ArrayList<Cele>(); // 属性列表
        
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = getDBConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            String[] rses;
            while (rs.next()) {
                DBField field = co.getField();
                field.setFieldName(rs.getString(1));
                String type = rs.getString(2);
                if (type.indexOf("(") > -1) {
                	String len = type.substring(type.indexOf("(") + 1,type.indexOf(")"));
                    type = type.substring(0, type.indexOf("(")).toLowerCase();
                    //设置其长度
                    if(type.indexOf("char") > -1 || type.indexOf("text") > -1 || type.indexOf("blob") > -1){
                    	field.setMaxLen(Integer.valueOf(len));
                    }
                }
                //设置数据库类型
                field.setDbType(type);
                //设置对应的JAVA类型
                rses = new String[]{"","",""};
                field.setJavaType(DBConfig.getJavaType(type, rses));
                //设置其类型限制
                if(!rses[0].equals("")){
                	if(field.getMaxLen() == null){
                		field.setMaxLen(Integer.valueOf(rses[0]));
                	}
                }
                //设置其最小值
                if(!rses[1].equals("")){
                	field.setMin(Integer.valueOf(rses[1]));
                }
                //设置最大值
                if(!rses[2].equals("")){
                	field.setMax(Integer.valueOf(rses[2]));
                }
                //空检查
                String temp = rs.getString(3);
                field.setAllowNull(temp != null && temp.equalsIgnoreCase("yes"));
                //主键检查
                temp = rs.getString(3);
                field.setKey(temp != null && temp.equalsIgnoreCase("PRI"));
                field.setAllowNull(!field.isKey());
                //默认值检查
                temp = rs.getString(4);
                if(temp != null && temp.indexOf("0000-00-00") > -1){
                	field.setDefault(temp);
                }
                elements.add(co);
                co = new Cele();
            }
        } catch (Exception e) {
            AntiaLog.error("查询表结构出错！表：" + table);
            AntiaLog.error(e.toString());
            DBException dbe = new DBException("005",e.toString());
            SysExceptionTool.addExceptionInfo(dbe);
            throw dbe;
        } finally {
            DBUtils.closeQuietly(connection,statement,rs);
        }
        return elements;
    }

    /**
     * 根据SQL,返回查询结果
     *
     * @param sql:SQL语句
     * @return list:(表信息列表，一条信息用一个列表代表)
     */
    public List<Map<Cele,Object>> find(String sql) throws DBException {
        Cele co = new Cele();
        List<Cele> attrs = new LinkedList<Cele>();
        List<Map<Cele,Object>> list = new ArrayList<Map<Cele,Object>>(); // 查询结果
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection =getDBConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            //存入列对象
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                co.getField().setDbType(metaData.getColumnTypeName(i)); // 类型名
                co.getField().setFieldName(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i)); // 列名
                attrs.add(co);
                co = new Cele();
            }
            //将ResultSet中的值存入LIST中
            String value;
            while (rs.next()) {
                Map<Cele,Object> elements = new HashMap<Cele,Object>();
                for (Cele attr : attrs) {
                    Cele ec1 = new Cele();
                    ec1.getField().setFieldName(attr.getField().getFieldName());
                    ec1.getField().setDbType(attr.getField().getDbType());
                    elements.put(ec1,rs.getObject(attr.getField().getFieldName()));
                }
                list.add(elements);
            }
            // 返回
        } catch (Exception e) {
            AntiaLog.error("查询数据出错！Sql：" + sql);
            AntiaLog.error(e.toString());
            DBException dbe = new DBException("005",e.toString());
            SysExceptionTool.addExceptionInfo(dbe);
            return null;
        } finally {
            DBUtils.closeQuietly(connection,statement,rs);
        }
        // 返回
        return list;
    }

    /**
     * 根据带有占位符的SQL,返回查询结果
     *
     * @param sql:SQL语句
     * @return List:(表信息列表，一条信息用一个列表代表)
     * @throws cn.antia.cep.db.DBException
     *          dbe
     */
    public List<Map<Cele,Object>> find(JdbcSql sql) throws DBException {
        Object[] array = sql.getValues();
        Cele co = new Cele();
        List<Cele> attrs = new ArrayList<Cele>();
        List<Map<Cele, Object>> list = new ArrayList<Map<Cele, Object>>(); // 查询结果
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            connection = getDBConnection();
            statement = connection.prepareStatement(sql.getSql());
            for (int i = 0; i < array.length; i++) {
                statement.setObject(i + 1, array[i]);
            }
            rs = statement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            //存入列对象
            DBField field = co.getField();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                field.setDbType(metaData.getColumnTypeName(i)); // 类型名
                field.setFieldName(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i)); // 列名
                attrs.add(co);
                co = new Cele();
            }
            //将ResultSet中的值存入LIST中
            while (rs.next()) {
                Map<Cele, Object> elements = new HashMap<Cele, Object>();
                for (Cele attr : attrs) {
                    Cele ec1 = new Cele();
                    ec1.getField().setFieldName(attr.getField().getFieldName());
                    ec1.getField().setDbType(attr.getField().getDbType());
                    elements.put(ec1, rs.getObject(attr.getField().getFieldName()));
                }
                list.add(elements);
            }
            // 返回
        } catch (Exception e) {
            e.get
            AntiaLog.error("查询数据出错！Sql：" + sql);
            AntiaLog.error(e.toString());
            DBException dbe = new DBException("005", e.toString());
            SysExceptionTool.addExceptionInfo(dbe);
            throw dbe;
        } finally {
            DBUtils.closeQuietly(connection,statement,rs);
        }
        // 返回
        return list;
    }

    public List<Map<Cele,Object>> find(JdbcSql sql,String orderField,boolean isAsc, int start, int end) throws DBException {
    	sql = getSqlGene().composeSql(sql,orderField,isAsc, start, end);
    	return find(sql);
	}

    /**
     * 根据表名与条件集取结果 更新数据
     *
     * @param table table
     * @param elements es
     * @return 修改行数
     * @throws cn.antia.cep.db.DBException e
     */
    public int update(String table, Map<Cele,Object> elements) throws DBException {
        JdbcSql sql = cwinSqlGene.updateSql(table, elements);
        return update(sql);
    }
    /**
     * @param table:表名
     * @param elements :字段与值
     * @return 影响行数
     */
    public int insert(String table, Map<Cele,Object> elements) throws DBException {
        JdbcSql jdbcSql = cwinSqlGene.insertSql(table, elements);
        return update(jdbcSql);
    }

    /**
     * 删除指定表中数据
     *
     * @param table:表名
     * @param id:主键
     * @return 影响行数
     * @throws cn.antia.cep.db.DBException e
     */
    public int delete(String table, int id) throws DBException {
        String sql = cwinSqlGene.deleteSql(table, id);
        return update(sql);
    }

    /**
     * 删除指定表中指定条件数据
     *
     * @param table:表名
     * @param elements 删除条件
     * @return 影响行数
     */
    public int delete(String table, Map<Cele,Object> elements){
        JdbcSql sql = cwinSqlGene.deleteSql(table, elements);
        return update(sql);
    }
    /**
	 * 查询	sql
	 * @param sql 简单的sql语句
	 * @return	结果列表:List<List<ElementComsat>>
	 * @param orderField orderField
     * @param isAsc asc order?
     * @param start 开始记录
	 * @param end 结束记录
	 * @throws DBException e
	 */
	public List<Map<Cele,Object>> find(String sql,String orderField,boolean isAsc,int start, int end) throws DBException {
		sql = getSqlGene().composeSql(sql,orderField,isAsc,start, end);
		return find(sql);
	}
	/**
	 * 查询最大记录数
	 * @param table		表名
	 * @param conditions	条件
	 * @return	记录数量
	 * @throws DBException e
     */
	public int findRecordCount(String table, Map<Cele,Object> conditions) throws DBException {
		JdbcSql jdbcSql = cwinSqlGene.findRecordCount(table, conditions);
		return getRecordCount(jdbcSql);
	}
}
